# -*- coding: utf-8 -*-
import json

from wt.connector.db_manager.db_untils_tools import DBUntilsTools
from wt.connector.data_connect import DataConnect


class DBMysqlManager(DataConnect, DBUntilsTools):

    def __init__(self):
        super().__init__()
        self.db_exec_res={}


    def create_ddl(self):
        """
        定义mysql插入字段类型
        :return:
        """
        col = {
            "id": "int(11) NOT NULL AUTO_INCREMENT,",
            "c_02_integer_col": "int(10) DEFAULT NULL,",
            "c_03_double_col": "double DEFAULT NULL,",
            "c_04_float_col": "float DEFAULT NULL,",
            "c_05_smallint_col": "smallint(5) DEFAULT NULL,",
            "c_06_bigint_col": "bigint(20) DEFAULT NULL,",
            "c_07_tinyint_col": "tinyint(3) DEFAULT NULL,",
            "c_08_decimal_col": "decimal(10,2) DEFAULT NULL,",
            "c_09_varchar_col": "varchar(50) DEFAULT NULL,",
            "c_10_date_col": "date DEFAULT NULL,",
            "c_11_datetime_col": "datetime DEFAULT NULL,",
            "c_12_timestamp_col": "int(10) DEFAULT NULL,",
            "": "PRIMARY KEY (`id`) \r\n ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;"
        }
        return col

    def generate_create_ddl(self, db, tableName):
        """
        拼接mysql ddl语句
        :param table:
        :return:
        """
        col = self.create_ddl()
        ddl_sql = 'create table {}.{} ( '.format(db, tableName)
        for key in col:
            line = key + " " + col[key] + "\r\n"
            ddl_sql = ddl_sql + line
        return ddl_sql

    def generate_dml_sql(self, databaseName, tableName, num):
        """
        批量生成 mysql dml 语句
        :param table:
        :param num:
        :return:
        """
        insert_sql = "insert INTO {} (id, c_02_integer_col, c_03_double_col, c_04_float_col, c_05_smallint_col, c_06_bigint_col," \
                     " c_07_tinyint_col, c_08_decimal_col, c_09_varchar_col, c_10_date_col, c_11_datetime_col, c_12_timestamp_col) VALUES ".format(
            tableName)

        res_num = self.get_count(databaseName, tableName)

        values_list = []
        for i in range(res_num + 1, res_num + num + 1):
            c_02_integer_val = self.get_random(-2147483648, 2147483648)
            c_03_double_val = self.get_random_float(-500, 500)
            c_04_float_val = self.get_random_float(-500, 500)
            c_05_smallint_val = self.get_random(-32768, 32767)
            c_06_bigint_val = self.get_random(-9223372036854775808, 9223372036854775807)
            c_07_tinyint_val = self.get_random(-128, 127)
            c_08_decimal_val = self.get_random_float(-500, 500)
            c_09_varchar_val = self.get_random_string(8)
            c_10_date_val = self.get_random_date()
            c_11_datetime_val = self.get_random_datetime()
            c_12_timestamp_col = self.get_random_timestamp()
            values_list.append("({}, {}, {}, {}, {}, {}, {}, {}, '{}', '{}', '{}' , '{}')".format(
                i, c_02_integer_val, c_03_double_val, c_04_float_val, c_05_smallint_val, c_06_bigint_val,
                c_07_tinyint_val, c_08_decimal_val, c_09_varchar_val, c_10_date_val, c_11_datetime_val,
                c_12_timestamp_col))

        insert_sql += ", ".join(values_list) + ";"

        return insert_sql

    def execute_create_and_insert_sql(self, db, table, num, is_recreate=None):
        """
        生成ddl、插入dml
        :param is_recreate:
        :param db:
        :param table:
        :param num:
        :return:
        """
        if is_recreate:
            self.generate_create_ddl(table)
        insert_sql = self.generate_dml_sql(db, table, num)
        self.exec_sql(db, insert_sql)

    def show_table_sql(self, db, table):
        query_sql = "show create table {}.{}".format(db, table)
        print(query_sql)
        res = self.exec_sql(db, query_sql)
        print(res)

    def exec_customer_sql(self, db, sql):
        res = self.exec_sql(db, sql)
        return res

    def set_primary_combkey(self, db, table):
        self.exec_sql(db, "alter table {}.{} change id id int".format(db, table))
        self.exec_sql(db, "alter table {}.{} drop primary key".format(db, table))
        add_combkey_sql = "ALTER TABLE {}.{} ADD CONSTRAINT primary_key PRIMARY KEY (id,c_02_integer_col)".format(db,
                                                                                                                  table)
        self.exec_sql(db, add_combkey_sql)

    def create_and_insert_sql(self, db, table, num, is_recreate=None):
        """
        生成ddl、插入dml
        :param is_recreate:
        :param db:
        :param table:
        :param num:
        :return:
        """
        # 删除表/新增表
        if is_recreate:
            ddl_sql = self.generate_create_ddl(table)
            self.exec_sql(db, "drop table {}".format(table))
            self.exec_sql(db, ddl_sql)
        insert_sql = self.generate_dml_sql(db, table, num)
        self.exec_sql(db, insert_sql)

    def get_count(self, db, table):
        sql = "select count(1) as cot from {}.{}".format(db, table)
        res = self.exec_sql(db, sql)
        if res['message'] == "Success":
            res_num = res['data'][0]["cot"]
            return res_num
        else:
            return None

    def execute_drop_table(self, db, table):
        sql = "drop table {}.{}".format(db, table)
        print(sql)
        res = self.exec_sql(db, sql)
        return self.set_return_massage(res)

    def execute_deleted_data(self, db, table):
        sql = ("delete FROM {}.{} WHERE id IN (SELECT id FROM (SELECT id FROM {}.{} ORDER BY id DESC LIMIT 100)"
               " AS subquery);".format(db, table, db, table))
        res = self.exec_sql(db, sql)
        return self.set_return_massage(res)

    def execute_update_data(self, db, table):
        sql1 = "desc {}.{}".format(db, table)
        res = self.exec_sql(db, sql1)
        if res['message'] != "Success":
            return False

        columns = [row['Field'] for row in res['data'] if row['Field'] != 'id']

        get_ids_sql = "select id FROM {}.{} ORDER BY id DESC LIMIT 10".format(db, table)
        ids_res = self.exec_sql(db, get_ids_sql)
        if ids_res['message'] != "Success":
            return False
        ids = [row['id'] for row in ids_res['data']]

        for id in ids:
            updates = []
            for col in columns:
                if col == 'c_02_integer_col':
                    updates.append("{} = {}".format(col, self.get_random(-2147483648, 2147483648)))
                elif col == 'c_03_double_col' or col == 'c_08_decimal_col':
                    updates.append("{} = {}".format(col, self.get_random_float(-500, 500)))
                elif col == 'c_04_float_col':
                    updates.append("{} = {}".format(col, self.get_random_float(-500, 500)))
                elif col == 'c_05_smallint_col':
                    updates.append("{} = {}".format(col, self.get_random(-32768, 32767)))
                elif col == 'c_06_bigint_col':
                    updates.append("{} = {}".format(col, self.get_random(-9223372036854775808, 9223372036854775807)))
                elif col == 'c_07_tinyint_col':
                    updates.append("{} = {}".format(col, self.get_random(-128, 127)))
                elif col == 'c_09_varchar_col':
                    updates.append("{} = '{}'".format(col, self.get_random_string(8)))
                elif col == 'c_10_date_col':
                    updates.append("{} = '{}'".format(col, self.get_random_date()))
                elif col == 'c_11_datetime_col':
                    updates.append("{} = '{}'".format(col, self.get_random_datetime()))
                elif col == 'c_12_timestamp_col':
                    updates.append("{} = '{}'".format(col, self.get_random_timestamp()))

            update_sql = "update {}.{} SET {} WHERE id = {}".format(db, table, ", ".join(updates), id)
            update_res = self.exec_sql(db, update_sql)
            if update_res['message'] != "Success":
                return False

        return True

    def get_db_data(self, db, table):
        sql = "select * FROM {}.{} ORDER BY id DESC LIMIT 10".format(db, table)
        res = self.exec_sql(db, sql)
        return self.set_return_massage(res)


if __name__ == '__main__':
    a = DBMysqlManager()
    sql = a.generate_create_ddl("wls.t1")
    print(a.exec_sql("qa_source", sql))
    # a.exec_customer_sql("qa_source", "ALTER TABLE qa_source.aaa1_ddl DROP id_11")
    # a.exec_customer_sql("qa_source", "ALTER TABLE qa_source.aaa1_ddl ADD COLUMN id_11 int(11) DEFAULT '1';")
    # a.create_and_insert_sql("qa_source", "mysql_base_string_date_split_2w", 20000, is_recreate=True)
    # a.get_count("qa_source", "auto_stream_mysql")
    # a.set_primary_combkey("qa_source", "v26_combkey_autocreate_starrocks")
